package jnpf.base.service.impl;

import com.alibaba.druid.pool.DruidDataSource;
import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.ds.GroupDataSource;
import jnpf.base.service.DbLinkService;
import jnpf.base.service.DbTableService;
import jnpf.base.util.dbutil.TableUtil;
import jnpf.config.ConfigValueUtil;
import jnpf.constant.MsgCode;
import jnpf.database.constant.DbFieldConst;
import jnpf.util.data.DataSourceContextHolder;
import jnpf.database.datatype.db.interfaces.DtInterface;
import jnpf.database.datatype.viewshow.ViewDataTypeEnum;
import jnpf.database.datatype.viewshow.constant.DtViewConst;
import jnpf.database.model.dbfield.DbFieldModel;
import jnpf.database.model.dbtable.DbTableFieldModel;
import jnpf.database.model.dto.DbConnDTO;
import jnpf.database.model.dto.PrepareSqlDTO;
import jnpf.database.model.page.DbTableDataForm;
import jnpf.database.model.page.JdbcPageMod;
import jnpf.database.source.DbBase;
import jnpf.database.source.impl.DbPostgre;
import jnpf.database.sql.enums.base.SqlComEnum;
import jnpf.database.sql.util.SqlFastUtil;
import jnpf.database.sql.util.SqlFrameUtil;
import jnpf.database.util.*;
import jnpf.exception.DataException;
import jnpf.util.JsonUtil;
import jnpf.util.ParameterUtil;
import jnpf.util.StringUtil;
import jnpf.util.UserProvider;
import lombok.Cleanup;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * 数据管理
 *
 * @author JNPF开发平台组
 * @version V3.1.0
 * @copyright 引迈信息技术有限公司
 * @date 2019年9月27日 上午9:18
 */
@Slf4j
@Service
public class DbTableServiceImpl implements DbTableService {

    @Autowired
    private DbLinkService dblinkService;
    @Autowired
    private DataSourceUtil dataSourceUtils;
    @Autowired
    private UserProvider userProvider;
    @Autowired
    private ConfigValueUtil configValueUtil;
    @Autowired
    private DbTableService dbTableService;
    @Autowired
    private DataSource dataSource;


    @Override
    public List<DbTableFieldModel> getList(String dbLinkId) throws Exception {
        DbConnDTO connDTO = getResource(dbLinkId);
        @Cleanup Connection conn = connDTO.getConn();
        List<DbTableFieldModel> list =  JdbcUtil.queryCustomMods(
                SqlFastUtil.getSysPreDTO(conn, SqlComEnum.TABLES, connDTO.getDbSource(), ""),
                DbTableFieldModel.class);
        for (DbTableFieldModel dbTableFieldModel : list) {
            dbTableFieldModel.setDbEncode(connDTO.getDbBase().getJnpfDbEncode());
        }
        // 排序
        return list.stream().sorted(Comparator.comparing(DbTableFieldModel::getTable)).collect(Collectors.toList());
    }

    @Override
    public DbTableFieldModel getTable(String dbLinkId, String table) throws Exception {
        DbConnDTO connDTO = getResource(dbLinkId);
        @Cleanup Connection conn = connDTO.getConn();
        List<DbTableFieldModel> dbTableFieldModelList =  JdbcUtil.queryCustomMods(
                SqlFastUtil.getSysPreDTO(conn, SqlComEnum.TABLE, connDTO.getDbSource(), table),
                DbTableFieldModel.class);
        if(dbTableFieldModelList.size() < 1){
            throw new DataException(MsgCode.DB010.get(connDTO.getDbSource().getDbName(), table));
        }
        DbTableFieldModel dbTableFieldModel = dbTableFieldModelList.get(0);
        dbTableFieldModel.setDbEncode(connDTO.getDbBase().getJnpfDbEncode());
        return dbTableFieldModel;
    }

    @Override
    public List<DbFieldModel> getFieldList(String dbLinkId, String table) throws Exception {
        DbConnDTO connDTO = getResource(dbLinkId);
        @Cleanup Connection conn = connDTO.getConn();
        return JdbcUtil.queryCustomMods(
                SqlFastUtil.getSysPreDTO(conn, SqlComEnum.FIELDS, connDTO.getDbSource(), table),
                DbFieldModel.class);
    }

    @Override
    public boolean isExistTable(String dbLinkId, String table) throws Exception {
        DbConnDTO connDTO = getResource(dbLinkId);
        @Cleanup Connection conn = connDTO.getConn();
        Integer total = JdbcUtil.queryOneInt(SqlFastUtil.getSysPreDTO(conn, SqlComEnum.EXISTS_TABLE, connDTO.getDbSource(), table), "TOTAL");
        return total > 0;
    }

    @Override
    public boolean dropExistsTable(String dbLinkId, String table) throws Exception {
        DbConnDTO connDTO = getResource(dbLinkId);
        @Cleanup Connection conn = connDTO.getConn();
        Integer total = JdbcUtil.queryOneInt(SqlFastUtil.getSysPreDTO(conn, SqlComEnum.EXISTS_TABLE, connDTO.getDbSource(), table), "TOTAL");
        if(total > 0){
            return SqlFastUtil.dropTable(conn, table);
        }
        return true;
    }

    @Override
    public List<Map<String, Object>> getData(DbTableDataForm dbTableDataForm, String dbLinkId, String table) throws Exception {
        DbConnDTO connDTO = getResource(dbLinkId);
        @Cleanup Connection conn = connDTO.getConn();
        JdbcPageMod<Map<String, Object>> result = new JdbcPageMod<>();
        String orderKey = dbTableDataForm.getField();
        if (conn != null) {
            List<Object> dataList = new ArrayList<>();
            if(connDTO.getDbBase().getJnpfDbEncode().equals(DbBase.POSTGRE_SQL)){
                table = DbPostgre.getTable(table);
                orderKey = DbPostgre.getTable(orderKey);
                String schema = connDTO.getDbSource().getDbSchema();
                if(StringUtil.isNotEmpty(schema)){
                    table = schema + "." + table;
                }
            }
            StringBuilder sql = new StringBuilder(SqlComEnum.SELECT_TABLE.getOutSql(table));
            //模糊查询
            if (!StringUtil.isEmpty(dbTableDataForm.getKeyword()) && !StringUtil.isEmpty(orderKey)) {
                sql.append(" where " + orderKey + " like ?");
                dataList.add("%" + SqlFrameUtil.keyWordTrim(dbTableDataForm.getKeyword()) + "%");
            }
            result = JdbcUtil.queryPage(new PrepareSqlDTO(conn, sql.toString(), dataList),  orderKey, (int) dbTableDataForm.getCurrentPage(), (int) dbTableDataForm.getPageSize(),
                    true, true);
        }
        return result.getDataList();
    }

    @Override
    public int getSum(String dbLinkId,String table)throws DataException {
        int sum = 0;
        try {
            DbConnDTO connDTO = getResource(dbLinkId);
            @Cleanup Connection conn = connDTO.getConn();
            if (conn != null) {
                String sql = SqlComEnum.COUNT_SIZE.getOutSql(table);
                PrepareSqlDTO dto = new PrepareSqlDTO(conn, sql);
                sum = JdbcUtil.queryOneInt(dto, "COUNT_SUM");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return sum;
    }

    /**=====================增删改========================**/

    @Override
    public void delete(String dbLinkId, String table) throws Exception {
        if (!sumExistFlag(dbLinkId, table)) {
            if (TableUtil.checkRepetition(table)) {
                throw new DataException("系统自带表,不允许被修改");
            }
        }else {
            throw new DataException("表已经被使用,不允许被编辑");
        }
        try {
            DbConnDTO connDTO = getResource(dbLinkId);
            @Cleanup Connection conn = connDTO.getConn();
            SqlFastUtil.dropTable(conn, table);
        }catch (Exception e){
            throw new DataException(e.getMessage());
        }
    }

    @Override
    public int createTable(DbTableFieldModel dbTableFieldModel) throws Exception {
        DbConnDTO connDTO = getResource(dbTableFieldModel.getDbLinkId());
        @Cleanup Connection conn = connDTO.getConn();
        // 数据库编码查询校验 (打印模板校验)
        String opeDb = connDTO.getDbBase().getJnpfDbEncode();
        String creDb =  dbTableFieldModel.getDbEncode();
        if(StringUtil.isNotEmpty(creDb)  && !creDb.equals(opeDb)){
            throw new DataException(MsgCode.DB008.get() + "：" + creDb +  " -> " + opeDb);
        }
        // 表重名判断
        if (isExistTable(dbTableFieldModel.getDbLinkId(), dbTableFieldModel.getTable())) {
            return 0;
        }
        // 表主键检验
        checkPrimary(dbTableFieldModel.getDbFieldModelList(), connDTO.getDbBase());
        // 创建表
        SqlFastUtil.creTable(conn, dbTableFieldModel);
        return 1;
    }

    @Override
    public void update(DbTableFieldModel dbTableFieldModel) throws Exception {
        String dbLink = dbTableFieldModel.getDbLinkId();
        DbConnDTO connDTO = getResource(dbLink);
        if (!sumExistFlag(dbLink, dbTableFieldModel.getUpdateOldTable())) {
            if (TableUtil.checkRepetition(dbTableFieldModel.getUpdateNewTable())) {
                throw new DataException("系统自带表,不允许被修改");
            }
        }else {
            throw new DataException("表已经被使用,不允许被编辑");
        }
        //临时表名
        String oldTable = dbTableFieldModel.getUpdateOldTable();
        String signTableName = oldTable + "_" + TableUtil.getStringRandom(5);
        @Cleanup Connection conn = connDTO.getConn();
        try{
            // 第一步：旧表改名（临时表名）(表名无法用?占位符的方式)
            SqlFastUtil.reTableName(conn, oldTable, signTableName);
            // 第二步：创建新表 (成功则删除旧表，失败则回滚)
            // 表主键检验
            checkPrimary(dbTableFieldModel.getDbFieldModelList(), connDTO.getDbBase());
            SqlFastUtil.creTable(conn, new DbTableFieldModel(
                    dbTableFieldModel.getUpdateNewTable(),
                    dbTableFieldModel.getComment(),
                    dbTableFieldModel.getDbFieldModelList()));
        }catch (Exception e){
            // 旧表改回原名(回滚conn.rollback()无法滚回ALTER语句等DDL操作语句)
            SqlFastUtil.reTableName(conn, signTableName, oldTable);
            throw e;
        }
        // 手动提交
        conn.commit();
        // 第三步：删除旧表
        SqlFastUtil.dropTable(conn, signTableName);
    }

    @Override
    public void addField(DbTableFieldModel dbTableFieldModel) throws Exception {
        String dbLink = dbTableFieldModel.getDbLinkId();
        DbConnDTO connDTO = getResource(dbLink);
        @Cleanup Connection conn = connDTO.getConn();
        SqlFastUtil.addField(conn, dbTableFieldModel.getUpdateNewTable(), dbTableFieldModel.getDbFieldModelList());
    }

    @Override
    public DbTableFieldModel getDbTableModel(String dbLinkId, String tableName)throws Exception {
        DbTableFieldModel dbTableModel = getList(dbLinkId).stream().filter(m -> m.getTable().equals(tableName)).findFirst().orElse(null);
        if(dbTableModel != null){
            dbTableModel.setDbFieldModelList(getFieldList(dbLinkId, tableName));
            return dbTableModel;
        }else {
            throw new DataException("未找到此表：" + tableName);
        }
    }

    /**================复用代码==================**/

    /**
     * 检查主键
     * @param tableFieldList  表字段集合
     * @throws DataException ignore
     */
    private void checkPrimary(List<DbFieldModel> tableFieldList, DbBase dbBase) throws Exception {
        // 默认主键为字符串类型
        // 主键会自动添加"非空"限制，所以不用做判断。(为空不加语句，且数据库默认字段可为空)
        int autoIncrementNum = 0;
        for(DbFieldModel field : tableFieldList) {
            if(field.getIsPrimaryKey()){
                field.setNullSign(DbFieldConst.NOT_NULL);
                if(field.getIsAutoIncrement() != null && field.getIsAutoIncrement()){
                    // 一张表最多只有一个自增主键，且此字段必须为primary key或者unique key。
                    autoIncrementNum += 1;
                    if(autoIncrementNum > 1){
                        throw new DataException("一张表最多只运行有一个自增主键");
                    }
                    if(!(field.getDataType().equals(DtViewConst.INT) || field.getDataType().equals(DtViewConst.BIGINT))){
                        throw new DataException("自增字段类型必须为数字类型");
                    }
                }else {

                    Method method = ViewDataTypeEnum.class.getMethod("getDt" + dbBase.getJnpfDbEncode() + "Enum");
                    DtInterface primaryVarcharEnum = (DtInterface)(method.invoke(ViewDataTypeEnum.VARCHAR));
                    // 非自增时必须为字符串类型的雪花ID
                    if(!((field.getDataType().equals(DtViewConst.VARCHAR) || field.getDataType().equals(primaryVarcharEnum.getDataType())))){
                        throw new DataException("非自增时雪花ID主键必须为\"字符串\"类型。");
                    }
                }
            }else {
                if(field.getIsAutoIncrement() != null && field.getIsAutoIncrement()){
                    throw new DataException("自增字段类型必须为主键");
                }
            }
        }
    }

    /**
     * 设置数据源
     * @param dbLinkId 数据连接id
     * @throws DataException ignore
     */
    @Override
    public DbConnDTO getResource(String dbLinkId) throws DataException {
        DataSourceUtil dbSourceOrDbLink;
        try {
            if("0".equals(dbLinkId)){
                //多租户是否开启
                String tenSource = configValueUtil.isMultiTenancy() ?
                        userProvider.get().getTenantDbConnectionString() : null;
                //默认数据库查询，从配置获取数据源信息
                dbSourceOrDbLink = JsonUtil.getJsonToBean(dataSourceUtils, DataSourceUtil.class);
                // 是系统默认的多租户
                if (!"true".equals(tenSource)) {
                    if(tenSource!=null && !"PostgreSQL".equals(dataSourceUtils.getDbType())){
                        dbSourceOrDbLink.setDbName(tenSource);
                    }
                } else {
                    DynamicRoutingDataSource dynamicRoutingDataSource = (DynamicRoutingDataSource) dataSource;
                    if (dynamicRoutingDataSource.getGroupDataSources().containsKey(DataSourceContextHolder.getDatasourceId() + "-master")) {
                        GroupDataSource groupDataSource = dynamicRoutingDataSource.getGroupDataSources().get(DataSourceContextHolder.getDatasourceId() + "-master");
                        if (groupDataSource.getDataSourceMap().size() > 0) {
                            String key = "";
                            for (String string : groupDataSource.getDataSourceMap().keySet()) {
                                key = string;
                                break;
                            }
                            DruidDataSource dataSource = (DruidDataSource)groupDataSource.getDataSourceMap().get(key);
                            String url = dataSource.getUrl();
                            String username = dataSource.getUsername();
                            String password = dataSource.getPassword();
                            String driverClassName = dataSource.getDriverClassName();
                            dbSourceOrDbLink.setUserName(username);
                            dbSourceOrDbLink.setPassword(password);
                            dbSourceOrDbLink.setPrepareUrl(url);
                            dbSourceOrDbLink.setDbName(dataSource.getConnection().getCatalog());
                            dbSourceOrDbLink.setDbType(GetDbInfo.getDbType(driverClassName));
                        }
                    }
                }
            }else {
                dbSourceOrDbLink = dblinkService.getInfo(dbLinkId);
            }
            return new DbConnDTO(DbTypeUtil.getDb(dbSourceOrDbLink),
                    dbSourceOrDbLink, ConnUtil.getConn(dbSourceOrDbLink));
        } catch (Exception e){
            throw new DataException(e.getMessage());
        }
    }

    /**
     * 存在数据不可修改
     *
     * @param dbLinkId   连接id
     * @param tableName 表名
     * @return ignore
     * @throws DataException ignore
     */
    private Boolean sumExistFlag(String dbLinkId, String tableName) throws Exception {
        DbTableFieldModel table = getTable(dbLinkId, tableName);
        //表存在判断
        if (table != null) {
            Integer sum = table.getSum();
            if (sum != null) {
                log.debug("表的数据总数sum是：" + sum);

            } else {
                sum = dbTableService.getSum(dbLinkId, tableName);
                /*throw new DataException("数据库表总数出现异常，导致无法删除。");*/
            }
            if (sum != 0 && sum > 0) {
                return true;
            } else {
                log.debug("表的数据表信息：" + table);
                return false;
            }
        } else {
            throw new DataException("该表已被删除，或者不存在，请刷新页面后再尝试。");
        }
    }

}
